package cn.ffcs.contract.dao;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import cn.ffcs.base.dao.BaseDAO;
import cn.ffcs.base.web.PageControl;
import cn.ffcs.contract.model.ActContract;
import cn.ffcs.sort.model.ProductSort;

/**
 * 合同管理DAO实现<br/>
 * <table>
 * <tr>
 * <td>负责人/principal:</td>
 * <td colspan="2">pany</td>
 * <tr>
 * <td>修改记录/revision:</td>
 * <td colspan="2"></td>
 * </tr>
 * <tr>
 * <td>日期:</td>
 * <td>修改人:</td>
 * <td>修改说明:</td>
 * </tr>
 * <tr>
 * <td></td>
 * <td></td>
 * <td></td>
 * </tr>
 * </table>
 * @author g.pany
 * @version $Id$
 */
public class ProjectDAO extends BaseDAO implements IProjectDAO
{
    /**
     * 获取字典类型实体类
     * @param b
     * @return
     */
    public String getEntityName()
    {
        return new ProductSort().getClass().getName();
    }
    
    /**
     * 根据id查询数据记录
     * {@inheritDoc}
     */
    public Map findObjectById(String tableName,String disCol,String cond){
        String sql="select "+disCol+" from "+tableName+cond;
//        System.out.println(sql);
        return fillMapByRs(disCol,executeQueryBySQL(sql));
    }
    
    /**
     * 1、归档合同查询
     * 归档：为空代表未归档;
     */
    public PageControl findVoaContract(HashMap conds,PageControl pageControl){
        String disCol="contractname,contractcode,partbname,applyuser,signdate,enddate,id";
        String sql=" from project_contract t,project_task a where t.id=a.keyval and a.status='已归档' ";
        return querySQL(null,disCol,sql,conds,pageControl,null);
    }
    
    /**
     * 2、合同会签进度查询
     * 查询条件：合同名称、产品类型、对方公司、起草人、提交时间。
     * 查询结果：合同名称、产品类型、目前会签部门、到达时间。
     * TODO 请输入方法说明
     * @param conds
     * @param pageControl
     * @return
     */
    public PageControl findContractCountersigned(HashMap conds,PageControl pageControl){
        String disCol="id,contractname,dept,receivetime,ccontracttype";
        String sql=" from project_contract t,project_task a where t.id=a.keyval ";
        if(conds!=null&&conds.containsKey("creadate")&&!"".equals(conds.get("creadate"))){
            sql+=" and trunc(creadate)=to_date('"+conds.get("creadate")+"','yyyy-mm-dd') ";
            conds.remove("creadate");
        }
        return querySQL(null,disCol,sql,conds,pageControl,null);
    }
    
    /**
     * 3、合同预警
     * 预警展示信息有标题、正式合同编号、合同起草人、合同状态、到期时间、顺延时间、处理操作。
     * TODO 请输入方法说明
     * @param conds
     * @param pageControl
     * @return
     */
    public PageControl findContractWarning(HashMap conds,PageControl pageControl){
//        String disCol="contractname,contractcode,partbname,applyuser,signdate,enddate,id,opertypename";
//        String sql=" from project_contract t,project_task a where t.id=a.keyval and enddate>sysdate and enddate<sysdate+30 ";
        String disCol="id,contract_name,creator,arrival_time,signatories_name,status,postpone_time,serial";
        String sql=" from t_contract_info t where arrival_time>sysdate and arrival_time<sysdate+30 ";
        
        return querySQL(null,disCol,sql,conds,pageControl,null);
    }
    
    /**
     * 4、合同模板查询
     * 查询条件：模板名称、模板类型
     * 查询结果：模板名称、合同类型、发布人、发布时间。
     * TODO 请输入方法说明
     * @param conds
     * @param pageControl
     * @return
     */
    public PageControl findContractTemplate(HashMap conds,PageControl pageControl){
        String disCol="templateid,templatename,frameflag,username,createdate";
        String sql=" from project_docu_template t where 1=1 ";
        return querySQL(null,disCol,sql,conds,pageControl,null);
    }
    
    /**
     * 5、经济合同查询
     * 查询条件：办理情况（待办/已办），标题，字号，送达时间（---至---），办理时间（---至---），当前状态。
     * 查询结果展示：序号，当前状态，标题，签约剩余工作日，字号，送达时间，缓急。
     * 查询结果展示：合同编号，当前状态，合同名称，到期时间，会签编号，送达时间。
     * TODO 请输入方法说明
     * @param conds
     * @param pageControl
     * @return
     */
    public PageControl findEconomicContracts(HashMap conds,PageControl pageControl){
        String disCol="id,contractcode,status,contractname,enddate,signcode,receiveTime";
        String sql=" from voacontract t,voacurrflow a where t.id=a.keyval ";
        if(conds!=null&&conds.containsKey("receiveTime1")&&!"".equals(conds.get("receiveTime1"))){
            sql+=" and trunc(receiveTime)>to_date('"+conds.get("receiveTime1")+"','yyyy-mm-dd') ";
            conds.remove("receiveTime1");
        }
        if(conds!=null&&conds.containsKey("receiveTime2")&&!"".equals(conds.get("receiveTime2"))){
            sql+=" and trunc(receiveTime)<to_date('"+conds.get("receiveTime2")+"','yyyy-mm-dd') ";
            conds.remove("receiveTime2");
        }
        return querySQL(null,disCol,sql,conds,pageControl," order by receiveTime desc");
    }

    /**
     * 6、经济合同查询:已办理
     * 查询条件：办理情况（待办/已办），标题，字号，送达时间（---至---），办理时间（---至---），当前状态。
     * 查询结果展示：序号，当前状态，标题，签约剩余工作日，字号，送达时间，缓急。
     * 查询结果展示：合同编号，当前状态，合同名称，到期时间，会签编号，送达时间。
     * TODO 请输入方法说明
     * @param conds
     * @param pageControl
     * @return
     */
    public PageControl findEconomicContractsByflow(HashMap conds,PageControl pageControl){
        String disCol="id,contractcode,status,contractname,enddate,signcode,receiveTime";
        String sql=" from voacontract t,voacurrflow a where t.id=a.keyval ";
        if(conds!=null&&conds.containsKey("receiveTime1")&&!"".equals(conds.get("receiveTime1"))){
            sql+=" and trunc(receiveTime)>to_date('"+conds.get("receiveTime1")+"','yyyy-mm-dd') ";
            conds.remove("receiveTime1");
        }
        if(conds!=null&&conds.containsKey("receiveTime2")&&!"".equals(conds.get("receiveTime2"))){
            sql+=" and trunc(receiveTime)<to_date('"+conds.get("receiveTime2")+"','yyyy-mm-dd') ";
            conds.remove("receiveTime2");
        }
        return querySQL(null,disCol,sql,conds,pageControl,null);
    }
    
    /**
     * 根据账号获取账户信息
     * {@inheritDoc}
     */
    public ActContract findCUserInfoById(String userId){ 
        String sql="select t.userid userid,t.userid_s userid_s,b.dept dept from prj_suser t,prj_suserdept a,prj_sdept b  where t.userid='"+userId+"' and t.userid=a.userid and a.deptid=b.deptid";
        ResultSet rs = executeQueryBySQL(sql);
        ActContract actContract=null;
        try
        {
            if (rs.next())
            {
                actContract=new ActContract();
                actContract.setUserId(rs.getString("userid"));
                actContract.setUserMk(rs.getString("userid_s"));
                actContract.setCurrDept(rs.getString("dept"));
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return actContract;
    }
    
    public String findIdBySigncode(String signcode){
        String sql="select id from voacontract t where t.signcode='"+signcode+"'";
        ResultSet rs = executeQueryBySQL(sql);
        ActContract actContract=null;
        try
        {
            if (rs!=null&&rs.next())
            {
                return rs.getString("id");
            }
        }
        catch (Exception e)
        {
            //e.printStackTrace();
        }
        return "";
    }
    
    /**
     * 根据查询条件组合sql查询语句，执行查询语句返回查询结果集
     * TODO 请输入方法说明
     * @param disCol
     * @param sql
     * @param conds
     * @param pageControl
     * @return
     */
    public PageControl querySQL(String prefix,String disCol,String sql,HashMap conds,PageControl pageControl,String orderBy){
        if(conds!=null){
            //遍历参数表
            Iterator i = conds.entrySet().iterator();
            while (i.hasNext())
            {
                Entry entry = (Entry)i.next();
                String key=(String)entry.getKey();
                if (conds.get(key) != null
                        && !StringUtils.isEmpty(conds.get(key).toString()))
                {
                    sql+=" and "+key+" like '%"+ conds.get(key)+"%'";
                }
            }
        }
        ResultSet rs = executeQueryBySQL("select count(*) " + sql);
        int count = 0;
        try
        {
            if (rs.next())
            {
                count = rs.getInt(1);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        pageControl.setTotal(count);
        pageControl=this.setPage(pageControl);
        if(prefix==null){
            prefix=disCol;
        }
        sql="select "+prefix+" from (select "+prefix+",rownum rownum_ from (select "+disCol+sql;
        if(orderBy!=null){
            sql+=orderBy;
        }
        sql+=") tt where rownum<"+pageControl.getEnd()+") where rownum_>="+pageControl.getStart();
        if(orderBy!=null){
            sql+=orderBy;
        }
//        System.out.println(sql);
        pageControl.setResults(fillListByRs(disCol,executeQueryBySQL(sql)));
        return pageControl;
    }

    /**
     * 计算分页的参数
     * TODO 请输入方法说明
     * @param pageControl
     * @return
     */
    public PageControl setPage(PageControl pageControl ){
        int count=pageControl.getTotal();
        int pageNo=pageControl.getPageNo();
        int pageSize=pageControl.getPageSize();
        int offset=0;
        int subNo=0;
        
        if(count>=pageNo*pageSize){
            offset=(pageNo-1)*pageSize+1;
        }else{
            offset=count/pageSize*pageSize;
        }
        if(count>=pageNo*pageSize){
            subNo=offset+pageSize;
        }else{
            subNo=count;
        }
        pageControl=new PageControl(count,pageNo,pageSize,1);
        pageControl.setStart(offset);
        pageControl.setEnd(subNo);
        return pageControl;
    }
    
    /**
     * 将查询结果ResultSet转化为List
     * TODO 请输入方法说明
     * @param str
     * @param rs
     * @return
     */
    public List fillListByRs(String str, ResultSet rs)
    {
        List list = new ArrayList();
        Map map = null;
        try
        {
            ResultSetMetaData rsmd = rs.getMetaData();// 获得查询字段集
            while (rs.next())
            {
                map = new HashMap();
                for (int i = 1; i <= rsmd.getColumnCount(); i++)
                {
                    map.put(rsmd.getColumnName(i).toLowerCase(), rs
                            .getString(i));
                }
                list.add(map);
            }
        }

        catch(Exception e){
            e.printStackTrace();
        }
        return list;
    }
    

    /**
     * 将查询结果ResultSet转化为Map
     * TODO 请输入方法说明
     * @param str
     * @param rs
     * @return
     */
    public Map fillMapByRs(String str, ResultSet rs)
    {
        Map map = new HashMap();
        try
        {
            ResultSetMetaData rsmd = rs.getMetaData();// 获得查询字段集
            if (rs.next())
            {
                for (int i = 1; i <= rsmd.getColumnCount(); i++)
                {
                    if(2004==rsmd.getColumnType(i)){
                        map.put(rsmd.getColumnName(i).toLowerCase(), rs.getBlob(i));
                    }
                    else{
                        map.put(rsmd.getColumnName(i).toLowerCase(), rs.getString(i));
                    }
                }
            }
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return map;
    }
    
    
    /**
     * 执行sql查询
     * @param sql
     * @return ResultSet
     */
    public ResultSet executeQueryBySQL(String sql)
    {
        java.sql.Connection conn = null;
        java.sql.Statement stmt = null;
        Session session = null;
        ResultSet rs=null;
        try
        {
            session = super.getSession();
            conn = session.connection();
            stmt = conn.createStatement();
            rs= stmt.executeQuery(sql);
        }
        catch (Exception e)
        {
            //e.printStackTrace();
            return null;
        }
        finally
        {
            if (session != null)
            {
                releaseSession(session);
            }
        }
        return rs;
    }


}
